
library(DBI)
library(RSQLite)
library(tidyverse)

con <- dbConnect(RSQLite::SQLite(), "./data/iso_standards.sqlite")

pallette <- c("skyblue", "salmon")

standards <- dbReadTable(con, "standards_status")

dbDisconnect(con)

abstracts <- standards %>%
  rename(text = abstract) %>%
  mutate(year = str_extract(publication_date, "[0-9]{4}")) %>%
  select(stdno, text, year, ics_number) %>%
  filter(text != "") %>%
  na.omit() %>%
  distinct(stdno, .keep_all = TRUE)

std_df <- readRDS("./data/standards1.rds")

p1 <- std_df %>%
  left_join(abstracts) %>%
  filter(year <= 2022) %>%
  mutate(year = as.numeric(year)) %>%
  group_by(type_text, year) %>%
  add_count() %>%
  group_by(year) %>%
  add_count() %>%
  unique() %>%
  mutate(share = (n/nn)*100) %>%
  filter(share != 100) %>%
  filter(type_text == "physical") %>%
  ungroup() %>%
  tidyr::complete(type_text, year = 1960:1975, fill = list(share = 100)) %>%
  mutate(type_text = str_to_sentence(type_text)) %>%
  rename(` ` = type_text) %>%
  ggplot(aes(year, share, color = ` `)) + 
  geom_point(alpha = 0.5) +
  geom_smooth(se = TRUE, alpha = .2) + 
  scale_color_manual(values = pallette[1]) +
  labs(y = "", x = "") +
  xlim(1960, 2025) +
  ylim(0,100) +
  ggtitle("Physical") +
  theme_bw() +
  theme(legend.position = "none",
        text = element_text(size = 20)) 

p2 <- std_df %>%
  left_join(abstracts) %>%
  filter(year <= 2022) %>%
  mutate(year = as.numeric(year)) %>%
  group_by(type_text, year) %>%
  add_count() %>%
  group_by(year) %>%
  add_count() %>%
  unique() %>%
  mutate(share = (n/nn)*100) %>%
  filter(type_text == "societal") %>%
  ungroup() %>%
  tidyr::complete(type_text, year = 1960:1975, fill = list(share = 0)) %>%
  group_by(year) %>%
  mutate(type_text = str_to_sentence(type_text)) %>%
  rename(` ` = type_text) %>%
  ggplot(aes(year, share, color = ` `)) + 
  geom_point(alpha = 0.5) +
  geom_smooth(se = TRUE, alpha = .2) + 
  scale_color_manual(values = pallette[2]) +
  labs(y = "", x = "") +
  xlim(1960, 2025) +
  ylim(0,100) +
  ggtitle("Societal") +
  theme_bw() +
  theme(legend.position = "none",
        text = element_text(size = 20)) 

cowplot::plot_grid(p1, p2, ncol = 2)
